package JDBC;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import SwingModel.CmsInstructor;
import SwingModel.CmsStudent;
import SwingModel.CmsUser;

public class PeopleActivities {
/*
	public static  List<CmsUser> getListOfPeople(String courseID) {
			
		List<CmsUser> newListOfPeople = new ArrayList();
		try{
			//Database Connection and Retrieve data
			CmsDatabaseConnection dCon = new CmsDatabaseConnection();
			PreparedStatement pstmt = dCon.getPreparedStatement();
			pstmt = dCon.connection.prepareStatement("SELECT U.UserName, U.FirstName, U.LastName, U.IsInstructor FROM User U, User_Courses C WHERE U.UserName = C.username AND C.CourseId=?");
			pstmt.setString(1,courseID);
			ResultSet resultSet = pstmt.executeQuery();

			for(int i = 0; resultSet.next(); i++){
				CmsUser user;
				String firstName;
				String lastName;
				String username;
				
				//get first name, last name, username:
				firstName = resultSet.getString("FirstName");
				lastName = resultSet.getString("LastName");
				username = resultSet.getString("UserName");
				
				//instantiate the user:
				if(resultSet.getInt("isInstructor") == 1) user = new CmsInstructor(username, firstName, lastName, false); //false == dont fetch list of courses
				else user = new CmsStudent(username, firstName, lastName, false); //false == dont fetch list of courses
				
				//add people entry to list of people
				newListOfPeople.add(user);
			}
		} catch(Exception e){}
		
		return newListOfPeople;
	}
	*/
	
	public static List<CmsUser> getListOfPeople(String courseID){
		List<CmsUser> listOfPeople = new ArrayList();
		try{
			//Database Connection and Retrieve data
			CmsDatabaseConnection dCon = new CmsDatabaseConnection();
			PreparedStatement pstmt = dCon.getPreparedStatement();
			pstmt = dCon.connection.prepareStatement("SELECT U.UserName, U.FirstName, U.LastName FROM User U, User_Courses C WHERE U.UserName = C.username AND C.CourseId=? AND U.IsInstructor = '1' order by U.FirstName asc;");
			pstmt.setString(1,courseID);
			ResultSet resultSet = pstmt.executeQuery();
			
			while(resultSet.next()){
				String firstName;
				String lastName;
				String username;
				
				//get first name, last name, username:
				firstName = resultSet.getString("FirstName");
				lastName = resultSet.getString("LastName");
				username = resultSet.getString("UserName");
				CmsUser user = new CmsInstructor(username, firstName, lastName, false);
				listOfPeople.add(user);
			}
			
			pstmt = dCon.connection.prepareStatement("SELECT U.UserName, U.FirstName, U.LastName FROM User U, User_Courses C WHERE U.UserName = C.username AND C.CourseId=? AND U.IsInstructor = '0' order by U.FirstName asc;");
			pstmt.setString(1,courseID);
			resultSet = pstmt.executeQuery();
			
			while(resultSet.next()){
				String firstName;
				String lastName;
				String username;
				
				//get first name, last name, username:
				firstName = resultSet.getString("FirstName");
				lastName = resultSet.getString("LastName");
				username = resultSet.getString("UserName");
				CmsUser user = new CmsStudent(username, firstName, lastName, false);
				listOfPeople.add(user);
			}
			
		}catch(Exception e){e.printStackTrace();}
		
		return listOfPeople;
	}
	
	public static List<CmsStudent> getListOfStudent(String courseID){
		List<CmsStudent> newListOfPeople = new ArrayList();
		try{
			//Database Connection and Retrieve data
			CmsDatabaseConnection dCon = new CmsDatabaseConnection();
			PreparedStatement pstmt = dCon.getPreparedStatement();
			pstmt = dCon.connection.prepareStatement("SELECT U.UserName, U.FirstName, U.LastName, U.IsInstructor FROM User U, User_Courses C WHERE U.IsInstructor = 0 AND U.UserName = C.username AND C.CourseId=?  order by U.FirstName asc");
			pstmt.setString(1,courseID);
			ResultSet resultSet = pstmt.executeQuery();

			for(int i = 0; resultSet.next(); i++){
				CmsStudent user;
				String firstName;
				String lastName;
				String username;
				
				//get first name, last name, username:
				firstName = resultSet.getString("FirstName");
				lastName = resultSet.getString("LastName");
				username = resultSet.getString("UserName");
				
				//instantiate the user:
				user = new CmsStudent(username, firstName, lastName, false); //false == dont fetch list of courses
				
				//add people entry to list of people
				newListOfPeople.add(user);
			}
		} catch(Exception e){}
		
		return newListOfPeople;
	}
	
	public static String getFullName(String username){
		String fullName = "";
		try{
			//Database Connection and Retrieve data
			CmsDatabaseConnection dCon = new CmsDatabaseConnection();
			PreparedStatement pstmt = dCon.getPreparedStatement();
			pstmt = dCon.connection.prepareStatement("SELECT FirstName, LastName FROM dbcms.User WHERE UserName = ?;");
			pstmt.setString(1,username);
			ResultSet resultSet = pstmt.executeQuery();

			for(int i = 0; resultSet.next(); i++){
				CmsStudent user;
				String firstName;
				String lastName;
				
				//get first name, last name, username:
				firstName = resultSet.getString("FirstName");
				lastName = resultSet.getString("LastName");
				
				fullName = firstName + " " + lastName;
			}
		} catch(Exception e){}
		return fullName;
	}
}
